﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DTO;
using System.Data;
using System.Data.SqlClient;

namespace DAL
{
    public class PageProvider:DataAccess
    {
        public PageProvider()
        {
            this.CacheDuration = Globals.Settings.Page.CacheDuration;
            this.EnableCaching = Globals.Settings.Page.EnableCaching;
            this.ConnectionString = Globals.Settings.ConnectionString;
        }

        #region IDataReader --> Object Pages
        protected virtual PagesDetails GetPageFromReader(IDataReader reader)
        {
            return new PagesDetails(
                (int)reader["PageID"],
                (DateTime)reader["AddedDate"],
                (string)reader["AddedBy"],
                (string)reader["Title"],
                (int)reader["Importance"],
                (string)reader["Abstract"],
                (string)reader["Body"],
                (int)reader["Option"]);
        }

        protected virtual List<PagesDetails> GetPageCollectionFromReader(IDataReader reader)
        {
            List<PagesDetails> Pages = new List<PagesDetails>();
            while (reader.Read())
                Pages.Add(GetPageFromReader(reader));
            return Pages;
        }

        #endregion

        #region Pages Method
        public List<PagesDetails> ListPage()
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("PagesList", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cn.Open();
                return GetPageCollectionFromReader(ExecuteReader(cmd));
            }
        }

        public PagesDetails PageGet(int PageID)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("PagesGet", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@PageID", SqlDbType.Int).Value = PageID;
                cn.Open();
                IDataReader reader = ExecuteReader(cmd, CommandBehavior.SingleRow);
                if (reader.Read())
                    return GetPageFromReader(reader);
                else
                    return null;
            }
        }

        public int InsertPage(PagesDetails Page)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("PagesInsert", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@AddedDate", SqlDbType.DateTime).Value = Page.AddedDate;
                cmd.Parameters.Add("@AddedBy", SqlDbType.NVarChar).Value = Page.AddedBy;
                cmd.Parameters.Add("@Title", SqlDbType.NVarChar).Value = Page.Title;
                cmd.Parameters.Add("@Importance", SqlDbType.Int).Value = Page.Importance;
                cmd.Parameters.Add("@Abstract", SqlDbType.NVarChar).Value = Page.Abstract;
                cmd.Parameters.Add("@Body", SqlDbType.NText).Value = Page.Body;
                cmd.Parameters.Add("@PageID", SqlDbType.Int).Direction = ParameterDirection.Output;
                cn.Open();
                int ret = ExecuteNonQuery(cmd);
                return (int)cmd.Parameters["@PageID"].Value;
            }
        }

        public bool UpdatePage(PagesDetails Page)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("PagesUpdate", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@Title", SqlDbType.NVarChar).Value = Page.Title;
                cmd.Parameters.Add("@Importance", SqlDbType.Int).Value = Page.Importance;
                cmd.Parameters.Add("@Abstract", SqlDbType.NVarChar).Value = Page.Abstract;
                cmd.Parameters.Add("@Body", SqlDbType.NText).Value = Page.Body;
                cmd.Parameters.Add("@PageID", SqlDbType.Int).Value = Page.PageID;                
                cn.Open();
                int ret = ExecuteNonQuery(cmd);
                return (ret == 1);
            }
        }

        public bool UpdatePageOption(PagesDetails Page)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("PagesUpdate_Option", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@Title", SqlDbType.NVarChar).Value = Page.Title;
                cmd.Parameters.Add("@Importance", SqlDbType.Int).Value = Page.Importance;
                cmd.Parameters.Add("@Abstract", SqlDbType.NVarChar).Value = Page.Abstract;
                cmd.Parameters.Add("@Body", SqlDbType.NText).Value = Page.Body;
                cmd.Parameters.Add("@Option", SqlDbType.Int).Value = Page.Option;
                cmd.Parameters.Add("@PageID", SqlDbType.Int).Value = Page.PageID;
                cn.Open();
                int ret = ExecuteNonQuery(cmd);
                return (ret == 1);
            }
        }

        public bool DeletePage(int PageID)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("PagesDelete", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@PageID", SqlDbType.Int).Value = PageID;
                cn.Open();
                int ret = ExecuteNonQuery(cmd);
                return (ret == 1);
            }
        }

        #endregion

        #region IDataReader --> Object Partners
        protected virtual PartnersDetails GetPartnerFromReader(IDataReader reader)
        {
            return new PartnersDetails(
                (int)reader["PartnerID"],
                (DateTime)reader["AddedDate"],
                (string)reader["AddedBy"],
                (string)reader["Title"],
                (string)reader["Logo"],
                (int)reader["Importance"],
                (string)reader["Website"],
                (string)reader["Description"],
                (int)reader["PartnerCatID"],
                (int)reader["Option"]);
        }

        protected virtual List<PartnersDetails> GetPartnerCollectionFromReader(IDataReader reader)
        {
            List<PartnersDetails> Partners = new List<PartnersDetails>();
            while (reader.Read())
                Partners.Add(GetPartnerFromReader(reader));
            return Partners;
        }

        #endregion

        #region Partners Method
        public List<PartnersDetails> ListPartner()
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("PartnersList", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cn.Open();
                return GetPartnerCollectionFromReader(ExecuteReader(cmd));
            }
        }

        public List<PartnersDetails> ListBanner()
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("[Partners_BannerList]", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cn.Open();
                return GetPartnerCollectionFromReader(ExecuteReader(cmd));
            }
        }

        public List<PartnersDetails> GetPartnerByPartnerCatID(int partnerCatID)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("PartnersGetByPartnerCatID", cn);
                cmd.Parameters.Add("@PartnerCatID", SqlDbType.Int).Value = partnerCatID;
                cmd.CommandType = CommandType.StoredProcedure;
                cn.Open();
                return GetPartnerCollectionFromReader(ExecuteReader(cmd));
            }
        }

        public PartnersDetails PartnerGet(int PartnerID)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("PartnersGet", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@PartnerID", SqlDbType.Int).Value = PartnerID;
                cn.Open();
                IDataReader reader = ExecuteReader(cmd, CommandBehavior.SingleRow);
                if (reader.Read())
                    return GetPartnerFromReader(reader);
                else
                    return null;
            }
        }

        public int InsertPartner(PartnersDetails Partner)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("PartnersInsert", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@AddedDate", SqlDbType.DateTime).Value = Partner.AddedDate;
                cmd.Parameters.Add("@AddedBy", SqlDbType.NVarChar).Value = Partner.AddedBy;
                cmd.Parameters.Add("@Title", SqlDbType.NVarChar).Value = Partner.Title;
                cmd.Parameters.Add("@Logo", SqlDbType.NVarChar).Value = Partner.Logo;
                cmd.Parameters.Add("@Importance", SqlDbType.Int).Value = Partner.Importance;
                cmd.Parameters.Add("@Website", SqlDbType.NVarChar).Value = Partner.Website;
                cmd.Parameters.Add("@Description", SqlDbType.NText).Value = Partner.Description;
                cmd.Parameters.Add("@PartnerCatID", SqlDbType.Int).Value = Partner.PartnerCatID;
                cmd.Parameters.Add("@Option", SqlDbType.Int).Value = Partner.Option;
                cmd.Parameters.Add("@PartnerID", SqlDbType.Int).Direction = ParameterDirection.Output;
                cn.Open();
                int ret = ExecuteNonQuery(cmd);
                return (int)cmd.Parameters["@PartnerID"].Value;
            }
        }

        public bool UpdatePartner(PartnersDetails Partner)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("PartnersUpdate", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@Title", SqlDbType.NVarChar).Value = Partner.Title;
                cmd.Parameters.Add("@Logo", SqlDbType.NVarChar).Value = Partner.Logo;
                cmd.Parameters.Add("@Importance", SqlDbType.Int).Value = Partner.Importance;
                cmd.Parameters.Add("@Website", SqlDbType.NVarChar).Value = Partner.Website;
                cmd.Parameters.Add("@Description", SqlDbType.NText).Value = Partner.Description;
                cmd.Parameters.Add("@PartnerCatID", SqlDbType.Int).Value = Partner.PartnerCatID;
                cmd.Parameters.Add("@PartnerID", SqlDbType.Int).Value = Partner.PartnerID;
                cn.Open();
                int ret = ExecuteNonQuery(cmd);
                return (ret == 1);
            }
        }

        public bool UpdatePartnerOption(PartnersDetails Partner)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("PartnersUpdate_Option", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@Title", SqlDbType.NVarChar).Value = Partner.Title;
                cmd.Parameters.Add("@Logo", SqlDbType.NVarChar).Value = Partner.Logo;
                cmd.Parameters.Add("@Importance", SqlDbType.Int).Value = Partner.Importance;
                cmd.Parameters.Add("@Website", SqlDbType.NVarChar).Value = Partner.Website;
                cmd.Parameters.Add("@Description", SqlDbType.NText).Value = Partner.Description;
                cmd.Parameters.Add("@PartnerCatID", SqlDbType.Int).Value = Partner.PartnerCatID;
                cmd.Parameters.Add("@PartnerID", SqlDbType.Int).Value = Partner.PartnerID;
                cmd.Parameters.Add("@Option", SqlDbType.Int).Value = Partner.Option;
                cn.Open();
                int ret = ExecuteNonQuery(cmd);
                return (ret == 1);
            }
        }

        public bool DeletePartner(int PartnerID)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("PartnersDelete", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@PartnerID", SqlDbType.Int).Value = PartnerID;
                cn.Open();
                int ret = ExecuteNonQuery(cmd);
                return (ret == 1);
            }
        }

        #endregion

        #region IDataReader --> Object PartnerCat
        protected virtual PartnerCatDetails GetPartnerCatFromReader(IDataReader reader)
        {
            return new PartnerCatDetails(
                (int)reader["PartnerCatID"],
                (DateTime)reader["AddedDate"],
                (string)reader["AddedBy"],
                (string)reader["Title"],
                (int)reader["Importance"],
                (string)reader["Description"],
                (string)reader["ImageUrl"]);
        }

        protected virtual List<PartnerCatDetails> GetPartnerCatCollectionFromReader(IDataReader reader)
        {
            List<PartnerCatDetails> PartnerCat = new List<PartnerCatDetails>();
            while (reader.Read())
                PartnerCat.Add(GetPartnerCatFromReader(reader));
            return PartnerCat;
        }

        #endregion

        #region PartnerCats Method
        public List<PartnerCatDetails> ListPartnerCat()
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("PartnerCatList", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cn.Open();
                return GetPartnerCatCollectionFromReader(ExecuteReader(cmd));
            }
        }

        public PartnerCatDetails GetPartnerCat(int PartnerCatID)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("PartnerCatGet", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@PartnerCatID", SqlDbType.Int).Value = PartnerCatID;
                cn.Open();
                IDataReader reader = ExecuteReader(cmd, CommandBehavior.SingleRow);
                if (reader.Read())
                    return GetPartnerCatFromReader(reader);
                else
                    return null;
            }
        }

        public int InsertPartnerCat(PartnerCatDetails PartnerCat)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("PartnerCatInsert", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@AddedDate", SqlDbType.DateTime).Value = PartnerCat.AddedDate;
                cmd.Parameters.Add("@AddedBy", SqlDbType.NVarChar).Value = PartnerCat.AddedBy;
                cmd.Parameters.Add("@Title", SqlDbType.NVarChar).Value = PartnerCat.Title;
                cmd.Parameters.Add("@Importance", SqlDbType.Int).Value = PartnerCat.Importance;
                cmd.Parameters.Add("@Description", SqlDbType.NVarChar).Value = PartnerCat.Description;
                cmd.Parameters.Add("@ImageUrl", SqlDbType.NVarChar).Value = PartnerCat.ImageUrl;
                cmd.Parameters.Add("@PartnerCatID", SqlDbType.Int).Direction = ParameterDirection.Output;
                cn.Open();
                int ret = ExecuteNonQuery(cmd);
                return (int)cmd.Parameters["@PartnerCatID"].Value;
            }
        }

        public bool UpdatePartnerCat(PartnerCatDetails PartnerCat)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("PartnerCatUpdate", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@Title", SqlDbType.NVarChar).Value = PartnerCat.Title;
                cmd.Parameters.Add("@Importance", SqlDbType.Int).Value = PartnerCat.Importance;
                cmd.Parameters.Add("@Description", SqlDbType.NVarChar).Value = PartnerCat.Description;
                cmd.Parameters.Add("@ImageUrl", SqlDbType.NVarChar).Value = PartnerCat.ImageUrl;
                cmd.Parameters.Add("@PartnerCatID", SqlDbType.Int).Value = PartnerCat.ID;
                cn.Open();
                int ret = ExecuteNonQuery(cmd);
                return (ret == 1);
            }
        }

        public bool DeletePartnerCat(int PartnerCatID)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("PartnerCatDelete", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@PartnerCatID", SqlDbType.Int).Value = PartnerCatID;
                cn.Open();
                int ret = ExecuteNonQuery(cmd);
                return (ret == 1);
            }
        }

        #endregion
    }
}
